"""
服务管理API路由
演示分类管理和复杂查询功能
"""
from typing import List, Optional
from fastapi import APIRouter, Depends, HTTPException, status, Query
from sqlalchemy.orm import Session
from sqlalchemy import desc, func, distinct

from ..database import get_db
from ..models.service import Service
from ..models.user import User
from ..schemas.service import (
    ServiceCreate, ServiceUpdate, ServiceResponse,
    ServiceListResponse, ServiceCategoryResponse
)
from ..dependencies import get_current_active_user


router = APIRouter(prefix="/api/services", tags=["服务管理"])


@router.get("", response_model=ServiceListResponse, summary="获取服务列表")
async def get_services(
    page: int = Query(1, ge=1, description="页码"),
    size: int = Query(10, ge=1, le=100, description="每页数量"),
    category: Optional[str] = Query(None, description="服务分类"),
    active_only: bool = Query(True, description="只显示激活的服务"),
    featured_only: bool = Query(False, description="只显示推荐服务"),
    db: Session = Depends(get_db)
):
    """
    获取服务列表，支持分类筛选

    演示了：
    1. 分类筛选功能
    2. 多条件组合查询
    3. 排序逻辑（推荐优先，然后按排序顺序）
    """
    # 构建查询
    query = db.query(Service)

    # 分类筛选
    if category:
        query = query.filter(Service.category == category)

    # 只显示激活的服务
    if active_only:
        query = query.filter(Service.is_active == True)

    # 只显示推荐服务
    if featured_only:
        query = query.filter(Service.is_featured == True)

    # 排序：推荐优先，然后按排序顺序，最后按创建时间
    query = query.order_by(
        desc(Service.is_featured),
        Service.sort_order,
        desc(Service.created_at)
    )

    # 计算总数
    total = query.count()

    # 分页
    offset = (page - 1) * size
    items = query.offset(offset).limit(size).all()

    # 计算总页数
    pages = (total + size - 1) // size

    return ServiceListResponse(
        items=items,
        total=total,
        page=page,
        size=size,
        pages=pages
    )


@router.get("/categories", response_model=List[ServiceCategoryResponse], summary="获取服务分类")
async def get_service_categories(
    active_only: bool = Query(True, description="只统计激活的服务"),
    db: Session = Depends(get_db)
):
    """
    获取所有服务分类及其数量

    演示了：
    1. 分组查询（GROUP BY）
    2. 聚合函数（COUNT）
    3. 条件统计
    """
    query = db.query(
        Service.category,
        func.count(Service.id).label('count')
    ).filter(Service.category.isnot(None))

    if active_only:
        query = query.filter(Service.is_active == True)

    categories = query.group_by(Service.category).all()

    return [
        ServiceCategoryResponse(category=cat, count=count)
        for cat, count in categories
    ]


@router.get("/{service_id}", response_model=ServiceResponse, summary="获取服务详情")
async def get_service(
    service_id: int,
    db: Session = Depends(get_db)
):
    """获取服务详情"""
    service = db.query(Service).filter(Service.id == service_id).first()

    if not service:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="服务不存在"
        )

    return service


@router.post("", response_model=ServiceResponse, summary="创建服务")
async def create_service(
    service_data: ServiceCreate,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """
    创建新服务

    需要管理员权限
    """
    # 创建服务对象
    db_service = Service(**service_data.dict())

    # 保存到数据库
    db.add(db_service)
    db.commit()
    db.refresh(db_service)

    return db_service


@router.put("/{service_id}", response_model=ServiceResponse, summary="更新服务")
async def update_service(
    service_id: int,
    service_data: ServiceUpdate,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """
    更新服务信息

    需要管理员权限
    """
    # 查找服务
    db_service = db.query(Service).filter(Service.id == service_id).first()

    if not db_service:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="服务不存在"
        )

    # 更新字段
    update_data = service_data.dict(exclude_unset=True)
    for field, value in update_data.items():
        setattr(db_service, field, value)

    # 保存更改
    db.commit()
    db.refresh(db_service)

    return db_service


@router.delete("/{service_id}", summary="删除服务")
async def delete_service(
    service_id: int,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """
    删除服务

    需要管理员权限
    """
    # 查找服务
    db_service = db.query(Service).filter(Service.id == service_id).first()

    if not db_service:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="服务不存在"
        )

    # 删除服务
    db.delete(db_service)
    db.commit()

    return {"message": "服务删除成功"}